cd "C:\DRS\"

*********************************************************
*** Own Sector ******************************************
use HoldingsDatabase.dta, clear

preserve
sort fundname year sector_own
gen y = 0
bysort fundname year: replace y = 1 if sector_own ~=sector_own[_n-1]
gen xbar = 0
replace xbar = 1 if sheld >0 
bysort  fundname year sector_own: egen ctryhldgsall = sum(xbar)
gen ctryhldgs = ctryhldgsall if y == 1
bys fundname year: egen tholdings = sum(ctryhldgs)
sort year fundname
gen t = 0
replace t = 1 if fundname ~=fundname[_n-1]

log using T3.log, replace
*** Table 3 Top Panel ***
*** First Column: No. of Classified Holdings ***

***** The numbers reported in the table are the differences in the medians between
***** the number of identified holdings and the number of non-classified holdings, 
***** which gives us the number of classified holdings 

** No. of Identified Holdings
tabstat tholdings if t==1 & fundtype2 == "Global" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "World" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Foreign" , stats(p50)
tabstat tholdings if t==1 & (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund"), stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Emerging Market" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Asia" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Europe" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Latin America" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Country Fund" , stats(p50)
tabstat tholdings if t==1 , stats(p50)
log close

gen xbar_u = 0
replace xbar_u = 1 if sheld >0 & sector_own ==.
bysort  fundname year sector_own: egen ctryhldgsall_u = sum(xbar_u)
gen ctryhldgs_u = ctryhldgsall_u if y == 1
bys fundname year: egen tholdings_u = sum(ctryhldgs_u)
gen nonclass = tholdings_u

log using T3.log, append
** No. of Identified, but not Classified Holdings
tabstat nonclass if t==1 & fundtype2 == "Global" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "World" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Foreign" , stats(p50)
tabstat nonclass if t==1 & (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund"), stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Emerging Market" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Asia" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Europe" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Latin America" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Country Fund" , stats(p50)
tabstat nonclass if t==1 , stats(p50)
log close
restore

bys fundname year: egen teste = sum(perc_netassets)
drop if teste > 110
drop teste

gen x = 0
replace x = 1 if sector_own ~=.
bys fundname year: egen total2 = sum(perc_netassets) if x == 1
gen pw2 = perc_netassets/total2
bysort fundname year sector_own: egen sw2 = sum(pw2)

sort fundname year sector_own
gen y = 0
bysort fundname year: replace y = 1 if sector_own ~=sector_own[_n-1]

gen xbar2 = 0
replace xbar2 = 1 if sheld >0 & sector_own ~=.
bysort  fundname year sector_own: egen ctryhldgsall2 = sum(xbar2)
gen ctryhldgs2 = ctryhldgsall2 if y == 1
bys fundname year: egen tholdings2 = sum(ctryhldgs2)

bys fundname year: egen s1a = max(ctryhldgs2) if sector_own == 1
bys fundname year: egen s2a = max(ctryhldgs2) if sector_own == 2
bys fundname year: egen s3a = max(ctryhldgs2) if sector_own == 3
bys fundname year: egen s4a = max(ctryhldgs2) if sector_own == 4
bys fundname year: egen s5a = max(ctryhldgs2) if sector_own == 5
bys fundname year: egen s6a = max(ctryhldgs2) if sector_own == 6
bys fundname year: egen s7a = max(ctryhldgs2) if sector_own == 7

bys fundname year: egen s1s = max(s1a)
bys fundname year: egen s2s = max(s2a)
bys fundname year: egen s3s = max(s3a)
bys fundname year: egen s4s = max(s4a)
bys fundname year: egen s5s = max(s5a)
bys fundname year: egen s6s = max(s6a)
bys fundname year: egen s7s = max(s7a)

replace s1s = 0 if s1s == .
replace s2s = 0 if s2s == .
replace s3s = 0 if s3s == .
replace s4s = 0 if s4s == .
replace s5s = 0 if s5s == .
replace s6s = 0 if s6s == .
replace s7s = 0 if s7s == .

gen ps1s = s1s/tholdings2 
gen ps2s = s2s/tholdings2 
gen ps3s = s3s/tholdings2 
gen ps4s = s4s/tholdings2 
gen ps5s = s5s/tholdings2 
gen ps6s = s6s/tholdings2 
gen ps7s = s7s/tholdings2 

rename ps1s S1weight
rename ps2s S2weight
rename ps3s S3weight
rename ps4s S4weight
rename ps5s S5weight
rename ps6s S6weight
rename ps7s S7weight

**** NO. of HOLDINGS ****
log using T3.log, append
*** Table 3 Top Panel ***
*** Median Portfolio Allocation Columns ***

foreach j of varlist S1weight-S7weight{
display as text "Global Funds, Sector " as result "`j'"
tabstat `j' if fundtype2 == "Global" & y == 1, stats(p50)
display as text "World Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "World" & y == 1, stats(p50)
display as text "Foreign Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Foreign" & y == 1, stats(p50)
display as text "Specialized Funds, Sector " as result "`j'"
tabstat `j' if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1, stats(p50)
display as text "Emerging Market Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Emerging Market" & y == 1, stats(p50)
display as text "Asia Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Asia" & y == 1, stats(p50)
display as text "Europe Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Europe" & y == 1, stats(p50)
display as text "Latin America Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Latin America" & y == 1, stats(p50)
display as text "Country Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Country Fund" & y == 1, stats(p50)
display as text "All Funds, Sector " as result "`j'"
tabstat `j' if y == 1, stats(p50)
}
log close

**** Portfolio Weights ****
log using AT4.log, replace
*** Appendix Table 4 Top Panel ***

*** Median Portfolio Allocation: Funds Own Sector Classification ***
tabstat sw2 if fundtype2 == "Global" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if fundtype7 == "World" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if fundtype7 == "Foreign" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sector_own) stats(p50)
tabstat sw2 if fundtype7 == "Emerging Market" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if fundtype7 == "Asia" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if fundtype7 == "Europe" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if fundtype7 == "Latin America" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if fundtype7 == "Country Fund" & y == 1, by(sector_own) stats(p50)
tabstat sw2 if y == 1, by(sector_own) stats(p50)
log close

drop y sw2

* Ordering Sectors Based on the Weight of the No. of Holdings
gen sectoralt  = 1 if sector_own == 2
replace sectoralt  = 2 if sector_own == 5
replace sectoralt  = 3 if sector_own == 4
replace sectoralt  = 4 if sector_own == 7
replace sectoralt  = 5 if sector_own == 1
replace sectoralt  = 6 if sector_own == 6
replace sectoralt  = 7 if sector_own == 3

bysort fundname year: cumul sectoralt  , generate(cumsic3) equal

bysort fundname year sectoralt: egen sw2 = sum(pw2)

gen cw1a = sw2 if sectoralt  == 1
gen cw2a = sw2 if sectoralt  == 2
gen cw3a = sw2 if sectoralt  == 3
gen cw4a = sw2 if sectoralt  == 4
gen cw5a = sw2 if sectoralt  == 5
gen cw6a = sw2 if sectoralt  == 6
gen cw7a = sw2 if sectoralt  == 7

bysort fundname year: egen cw1 = max(cw1a)
bysort fundname year: egen cw2 = max(cw2a)
bysort fundname year: egen cw3 = max(cw3a)
bysort fundname year: egen cw4 = max(cw4a)
bysort fundname year: egen cw5 = max(cw5a)
bysort fundname year: egen cw6 = max(cw6a)
bysort fundname year: egen cw7 = max(cw7a)

replace cw1 = 0 if cw1 == .
replace cw2 = 0 if cw2 == .
replace cw3 = 0 if cw3 == .
replace cw4 = 0 if cw4 == .
replace cw5 = 0 if cw5 == .
replace cw6 = 0 if cw6 == .
replace cw7 = 0 if cw7 == .

gen cwf2 = cw1 if sectoralt  == 1
replace cwf2 = cw1 + cw2 if sectoralt  == 2
replace cwf2 = cw1 + cw2+ cw3 if sectoralt  == 3
replace cwf2 = cw1 + cw2+ cw3 + cw4 if sectoralt  == 4
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 if sectoralt  == 5
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 if sectoralt  == 6
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 + cw7 if sectoralt  == 7

drop cw1-cw7 cw1a-cw7a

sort fundname year sectoralt
gen y = 0
bysort fundname year: replace y = 1 if sectoralt~=sectoralt[_n-1]


log using T3.log, append
*** Table 3 Bottom Panel ***
*** Cumulative Distribution of the Number of Holdings: Global vs. Specialized Funds ***
tabstat cumsic3 if fundtype2 == "Global" & y == 1, by(sectoralt) stats(p50)
tabstat cumsic3 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund")& y == 1 , by(sectoralt) stats(p50)
log close

log using AF2.log, replace
*** Appendix Figure 2 Top Panel ***
*** Cumulative Distribution of the Mutual Fund Assets: Global vs. Specialized Funds ***
*** Based on Funds Own Sector Classification***
tabstat cwf2 if fundtype2 == "Global" & y == 1, by(sectoralt) stats(p50)
tabstat cwf2 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sectoralt) stats(p50)
log close

drop x total2 pw2 xbar2 ctryhldgsall2 ctryhldgs2 tholdings2 
drop s1a-s7a s1s-s7s S1weight-S7weight
drop sectoralt cumsic3 sw2 cwf2 y

*********************************************************
*** Most Cited Sector ***********************************
use HoldingsDatabase.dta, clear

preserve
sort fundname year sector_unique
gen y = 0
bysort fundname year: replace y = 1 if sector_unique ~=sector_unique[_n-1]
gen xbar = 0
replace xbar = 1 if sheld >0 
bysort  fundname year sector_unique: egen ctryhldgsall = sum(xbar)
gen ctryhldgs = ctryhldgsall if y == 1
bys fundname year: egen tholdings = sum(ctryhldgs)
sort year fundname
gen t = 0
replace t = 1 if fundname ~=fundname[_n-1]

log using AT3.log, replace
*** Top Panel ***
*** First Column: No. of Classified Holdings ***

***** The numbers reported in the table are the differences in the medians between
***** the number of identified holdings and the number of non-classified holdings, 
***** which gives us the number of classified holdings 

** No. of Identified Holdings
tabstat tholdings if t==1 & fundtype2 == "Global" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "World" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Foreign" , stats(p50)
tabstat tholdings if t==1 & (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund"), stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Emerging Market" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Asia" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Europe" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Latin America" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Country Fund" , stats(p50)
tabstat tholdings if t==1 , stats(p50)
log close


gen xbar_u = 0
replace xbar_u = 1 if sheld >0 & sector_unique ==.
bysort  fundname year sector_unique: egen ctryhldgsall_u = sum(xbar_u)
gen ctryhldgs_u = ctryhldgsall_u if y == 1
bys fundname year: egen tholdings_u = sum(ctryhldgs_u)
gen nonclass = tholdings_u

log using AT3.log, append
** No. of Identified, but not Classified Holdings
tabstat nonclass if t==1 & fundtype2 == "Global" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "World" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Foreign" , stats(p50)
tabstat nonclass if t==1 & (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund"), stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Emerging Market" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Asia" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Europe" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Latin America" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Country Fund" , stats(p50)
tabstat nonclass if t==1 , stats(p50)
log close
restore

bys fundname year: egen teste = sum(perc_netassets)
drop if teste > 110
drop teste

gen x = 0
replace x = 1 if sector_unique ~=.
by fundname year: egen total2 = sum(perc_netassets) if x == 1
gen pw2 = perc_netassets/total2
bysort fundname year sector_unique: egen sw2 = sum(pw2)

sort fundname year sector_unique
gen y = 0
bysort fundname year: replace y = 1 if sector_unique ~=sector_unique[_n-1]

gen xbar2 = 0
replace xbar2 = 1 if sheld >0 & sector_unique ~=.
bysort  fundname year sector_unique: egen ctryhldgsall2 = sum(xbar2)
gen ctryhldgs2 = ctryhldgsall2 if y == 1
bys fundname year: egen tholdings2 = sum(ctryhldgs2)

bys fundname year: egen s1a = max(ctryhldgs2) if sector_unique == 1
bys fundname year: egen s2a = max(ctryhldgs2) if sector_unique == 2
bys fundname year: egen s3a = max(ctryhldgs2) if sector_unique == 3
bys fundname year: egen s4a = max(ctryhldgs2) if sector_unique == 4
bys fundname year: egen s5a = max(ctryhldgs2) if sector_unique == 5
bys fundname year: egen s6a = max(ctryhldgs2) if sector_unique == 6
bys fundname year: egen s7a = max(ctryhldgs2) if sector_unique == 7

bys fundname year: egen s1s = max(s1a)
bys fundname year: egen s2s = max(s2a)
bys fundname year: egen s3s = max(s3a)
bys fundname year: egen s4s = max(s4a)
bys fundname year: egen s5s = max(s5a)
bys fundname year: egen s6s = max(s6a)
bys fundname year: egen s7s = max(s7a)

replace s1s = 0 if s1s == .
replace s2s = 0 if s2s == .
replace s3s = 0 if s3s == .
replace s4s = 0 if s4s == .
replace s5s = 0 if s5s == .
replace s6s = 0 if s6s == .
replace s7s = 0 if s7s == .

gen ps1s = s1s/tholdings2 
gen ps2s = s2s/tholdings2 
gen ps3s = s3s/tholdings2 
gen ps4s = s4s/tholdings2 
gen ps5s = s5s/tholdings2 
gen ps6s = s6s/tholdings2 
gen ps7s = s7s/tholdings2 

rename ps1s S1weight
rename ps2s S2weight
rename ps3s S3weight
rename ps4s S4weight
rename ps5s S5weight
rename ps6s S6weight
rename ps7s S7weight


**** NO. of HOLDINGS ****
log using AT3.log, append
*** Appendix Table 3 Top Panel ***
*** Median Portfolio Allocation Columns ***
foreach j of varlist S1weight-S7weight{
display as text "Global Funds, Sector " as result "`j'"
tabstat `j' if fundtype2 == "Global" & y == 1, stats(p50)
display as text "World Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "World" & y == 1, stats(p50)
display as text "Foreign Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Foreign" & y == 1, stats(p50)
display as text "Specialized Funds, Sector " as result "`j'"
tabstat `j' if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1, stats(p50)
display as text "Emerging Market Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Emerging Market" & y == 1, stats(p50)
display as text "Asia Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Asia" & y == 1, stats(p50)
display as text "Europe Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Europe" & y == 1, stats(p50)
display as text "Latin America Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Latin America" & y == 1, stats(p50)
display as text "Country Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Country Fund" & y == 1, stats(p50)
display as text "All Funds, Sector " as result "`j'"
tabstat `j' if y == 1, stats(p50)
}
log close

**** Portfolio Weights ****
log using AT4.log, append
*** Appendix Table 4 Middle Panel ***

*** Median Portfolio Allocation: Most Cited Sector Classification ***
tabstat sw2 if fundtype2 == "Global" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if fundtype7 == "World" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if fundtype7 == "Foreign" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sector_unique) stats(p50)
tabstat sw2 if fundtype7 == "Emerging Market" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if fundtype7 == "Asia" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if fundtype7 == "Europe" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if fundtype7 == "Latin America" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if fundtype7 == "Country Fund" & y == 1, by(sector_unique) stats(p50)
tabstat sw2 if y == 1, by(sector_unique) stats(p50)
log close

drop y sw2

* Ordering Sectors Based on the Weight of the No. of Holdings
gen sectoralt  = 1 if sector_unique == 2
replace sectoralt  = 2 if sector_unique == 7
replace sectoralt  = 3 if sector_unique == 4
replace sectoralt  = 4 if sector_unique == 5
replace sectoralt  = 5 if sector_unique == 1
replace sectoralt  = 6 if sector_unique == 6
replace sectoralt  = 7 if sector_unique == 3

bysort fundname year: cumul sectoralt  , generate(cumsic3) equal

bysort fundname year sectoralt: egen sw2 = sum(pw2)

gen cw1a = sw2 if sectoralt  == 1
gen cw2a = sw2 if sectoralt  == 2
gen cw3a = sw2 if sectoralt  == 3
gen cw4a = sw2 if sectoralt  == 4
gen cw5a = sw2 if sectoralt  == 5
gen cw6a = sw2 if sectoralt  == 6
gen cw7a = sw2 if sectoralt  == 7

bysort fundname year: egen cw1 = max(cw1a)
bysort fundname year: egen cw2 = max(cw2a)
bysort fundname year: egen cw3 = max(cw3a)
bysort fundname year: egen cw4 = max(cw4a)
bysort fundname year: egen cw5 = max(cw5a)
bysort fundname year: egen cw6 = max(cw6a)
bysort fundname year: egen cw7 = max(cw7a)

replace cw1 = 0 if cw1 == .
replace cw2 = 0 if cw2 == .
replace cw3 = 0 if cw3 == .
replace cw4 = 0 if cw4 == .
replace cw5 = 0 if cw5 == .
replace cw6 = 0 if cw6 == .
replace cw7 = 0 if cw7 == .

gen cwf2 = cw1 if sectoralt  == 1
replace cwf2 = cw1 + cw2 if sectoralt  == 2
replace cwf2 = cw1 + cw2+ cw3 if sectoralt  == 3
replace cwf2 = cw1 + cw2+ cw3 + cw4 if sectoralt  == 4
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 if sectoralt  == 5
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 if sectoralt  == 6
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 + cw7 if sectoralt  == 7

drop cw1-cw7 cw1a-cw7a

sort fundname year sectoralt
gen y = 0
bysort fundname year: replace y = 1 if sectoralt~=sectoralt[_n-1]


log using AF1.log, replace
*** Appendix Figure 1 Top Panel ***
*** Cumulative Distribution of the Number of Holdings: Global vs. Specialized Funds ***
*** Based on the Most Cited Classification of Sectors***

tabstat cumsic3 if fundtype2 == "Global" & y == 1, by(sectoralt) stats(p50)
tabstat cumsic3 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sectoralt) stats(p50)
log close

log using AF2.log, append
*** Appendix Figure 2 Middle Panel ***
*** Cumulative Distribution of Mutual Fund Assets: Global vs. Specialized Funds ***
*** Based on the Most Cited Classification of Sectors***
tabstat cwf2 if fundtype2 == "Global" & y == 1, by(sectoralt) stats(p50)
tabstat cwf2 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sectoralt) stats(p50)
log close

drop x total2 pw2 xbar2 ctryhldgsall2 ctryhldgs2 tholdings2 
drop s1a-s7a s1s-s7s S1weight-S7weight
drop sectoralt cumsic3 sw2 cwf2 y

*********************************************************
*** SIC Sector ******************************************
use HoldingsDatabase.dta, clear

preserve
sort fundname year sic_sector
gen y = 0
bysort fundname year: replace y = 1 if sic_sector ~=sic_sector[_n-1]
gen xbar = 0
replace xbar = 1 if sheld >0 
bysort  fundname year sic_sector: egen ctryhldgsall = sum(xbar)
gen ctryhldgs = ctryhldgsall if y == 1
bys fundname year: egen tholdings = sum(ctryhldgs)
sort year fundname
gen t = 0
replace t = 1 if fundname ~=fundname[_n-1]

log using AT3.log, append
*** Bottom Panel ***
*** First Column: No. of Classified Holdings ***

***** The numbers reported in the table are the differences in the medians between
***** the number of identified holdings and the number of non-classified holdings, 
***** which gives us the number of classified holdings 

** No. of Identified Holdings
tabstat tholdings if t==1 & fundtype2 == "Global" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "World" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Foreign" , stats(p50)
tabstat tholdings if t==1 & (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund"), stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Emerging Market" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Asia" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Europe" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Latin America" , stats(p50)
tabstat tholdings if t==1 & fundtype7 == "Country Fund" , stats(p50)
tabstat tholdings if t==1 , stats(p50)
log close


gen xbar_u = 0
replace xbar_u = 1 if sheld >0 & sic_sector ==.
bysort  fundname year sic_sector: egen ctryhldgsall_u = sum(xbar_u)
gen ctryhldgs_u = ctryhldgsall_u if y == 1
bys fundname year: egen tholdings_u = sum(ctryhldgs_u)
gen nonclass = tholdings_u

log using AT3.log, append
** No. of Identified, but not Classified Holdings
tabstat nonclass if t==1 & fundtype2 == "Global" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "World" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Foreign" , stats(p50)
tabstat nonclass if t==1 & (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund"), stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Emerging Market" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Asia" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Europe" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Latin America" , stats(p50)
tabstat nonclass if t==1 & fundtype7 == "Country Fund" , stats(p50)
tabstat nonclass if t==1 , stats(p50)
log close
restore


bys fundname year: egen teste = sum(perc_netassets)
drop if teste > 110
drop teste

gen x = 0
replace x = 1 if sic_sector ~=.
by fundname year: egen total2 = sum(perc_netassets) if x == 1
gen pw2 = perc_netassets/total2
bysort fundname year sic_sector: egen sw2 = sum(pw2)

sort fundname year sic_sector
gen y = 0
bysort fundname year: replace y = 1 if sic_sector ~=sic_sector[_n-1]

gen xbar2 = 0
replace xbar2 = 1 if sheld >0 & sic_sector ~=.
bysort  fundname year sic_sector: egen ctryhldgsall2 = sum(xbar2)
gen ctryhldgs2 = ctryhldgsall2 if y == 1
bys fundname year: egen tholdings2 = sum(ctryhldgs2)

by fundname year: egen s1a = max(ctryhldgs2) if sic_sector == 1
by fundname year: egen s2a = max(ctryhldgs2) if sic_sector == 2
by fundname year: egen s3a = max(ctryhldgs2) if sic_sector == 3
by fundname year: egen s4a = max(ctryhldgs2) if sic_sector == 4
by fundname year: egen s5a = max(ctryhldgs2) if sic_sector == 5
by fundname year: egen s6a = max(ctryhldgs2) if sic_sector == 6
by fundname year: egen s7a = max(ctryhldgs2) if sic_sector == 7
by fundname year: egen s8a = max(ctryhldgs2) if sic_sector == 8
by fundname year: egen s9a = max(ctryhldgs2) if sic_sector == 9
by fundname year: egen s10a = max(ctryhldgs2) if sic_sector == 10

by fundname year: egen s1s = max(s1a)
by fundname year: egen s2s = max(s2a)
by fundname year: egen s3s = max(s3a)
by fundname year: egen s4s = max(s4a)
by fundname year: egen s5s = max(s5a)
by fundname year: egen s6s = max(s6a)
by fundname year: egen s7s = max(s7a)
by fundname year: egen s8s = max(s8a)
by fundname year: egen s9s = max(s9a)
by fundname year: egen s10s = max(s10a)

replace s1s = 0 if s1s == .
replace s2s = 0 if s2s == .
replace s3s = 0 if s3s == .
replace s4s = 0 if s4s == .
replace s5s = 0 if s5s == .
replace s6s = 0 if s6s == .
replace s7s = 0 if s7s == .
replace s8s = 0 if s8s == .
replace s9s = 0 if s9s == .
replace s10s = 0 if s10s == .

gen ps1s = s1s/tholdings2 
gen ps2s = s2s/tholdings2 
gen ps3s = s3s/tholdings2 
gen ps4s = s4s/tholdings2 
gen ps5s = s5s/tholdings2 
gen ps6s = s6s/tholdings2 
gen ps7s = s7s/tholdings2 
gen ps8s = s8s/tholdings2 
gen ps9s = s9s/tholdings2 
gen ps10s = s10s/tholdings2 

rename ps1s S1weight
rename ps2s S2weight
rename ps3s S3weight
rename ps4s S4weight
rename ps5s S5weight
rename ps6s S6weight
rename ps7s S7weight
rename ps8s S8weight
rename ps9s S9weight
rename ps10s S10weight

**** NO. of HOLDINGS ****
log using AT3.log, append
*** Appendix Table 3 Bottom Panel ***
*** Median Portfolio Allocation Columns ***
foreach j of varlist S1weight-S10weight{
display as text "Global Funds, Sector " as result "`j'"
tabstat `j' if fundtype2 == "Global" & y == 1, stats(p50)
display as text "World Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "World" & y == 1, stats(p50)
display as text "Foreign Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Foreign" & y == 1, stats(p50)
display as text "Specialized Funds, Sector " as result "`j'"
tabstat `j' if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1, stats(p50)
display as text "Emerging Market Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Emerging Market" & y == 1, stats(p50)
display as text "Asia Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Asia" & y == 1, stats(p50)
display as text "Europe Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Europe" & y == 1, stats(p50)
display as text "Latin America Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Latin America" & y == 1, stats(p50)
display as text "Country Funds, Sector " as result "`j'"
tabstat `j' if fundtype7 == "Country Fund" & y == 1, stats(p50)
display as text "All Funds, Sector " as result "`j'"
tabstat `j' if y == 1, stats(p50)
}
log close

**** Portfolio Weights ****
log using AT4.log, append
*** Appendix Table 4 Bottom Panel ***

*** Median Portfolio Allocation: SIC Classification of Sectors ***
tabstat sw2 if fundtype2 == "Global" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if fundtype7 == "World" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if fundtype7 == "Foreign" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sic_sector) stats(p50)
tabstat sw2 if fundtype7 == "Emerging Market" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if fundtype7 == "Asia" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if fundtype7 == "Europe" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if fundtype7 == "Latin America" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if fundtype7 == "Country Fund" & y == 1, by(sic_sector) stats(p50)
tabstat sw2 if y == 1, by(sic_sector) stats(p50)
log close

drop y sw2

* Ordering Sectors Based on the Weight of the No. of Holdings
gen sectoralt  = 1 if sic_sector == 4
replace sectoralt  = 2 if sic_sector == 8
replace sectoralt  = 3 if sic_sector == 5
replace sectoralt  = 4 if sic_sector == 9
replace sectoralt  = 5 if sic_sector == 7
replace sectoralt  = 6 if sic_sector == 6
replace sectoralt  = 7 if sic_sector == 2
replace sectoralt  = 8 if sic_sector == 3
replace sectoralt  = 9 if sic_sector == 10
replace sectoralt  = 10 if sic_sector == 1

bysort fundname year: cumul sectoralt  , generate(cumsic3) equal

bysort fundname year sectoralt: egen sw2 = sum(pw2)

gen cw1a = sw2 if sectoralt  == 1
gen cw2a = sw2 if sectoralt  == 2
gen cw3a = sw2 if sectoralt  == 3
gen cw4a = sw2 if sectoralt  == 4
gen cw5a = sw2 if sectoralt  == 5
gen cw6a = sw2 if sectoralt  == 6
gen cw7a = sw2 if sectoralt  == 7
gen cw8a = sw2 if sectoralt  == 8
gen cw9a = sw2 if sectoralt  == 9
gen cw10a = sw2 if sectoralt  == 10

bysort fundname year: egen cw1 = max(cw1a)
bysort fundname year: egen cw2 = max(cw2a)
bysort fundname year: egen cw3 = max(cw3a)
bysort fundname year: egen cw4 = max(cw4a)
bysort fundname year: egen cw5 = max(cw5a)
bysort fundname year: egen cw6 = max(cw6a)
bysort fundname year: egen cw7 = max(cw7a)
bysort fundname year: egen cw8 = max(cw8a)
bysort fundname year: egen cw9 = max(cw9a)
bysort fundname year: egen cw10 = max(cw10a)

replace cw1 = 0 if cw1 == .
replace cw2 = 0 if cw2 == .
replace cw3 = 0 if cw3 == .
replace cw4 = 0 if cw4 == .
replace cw5 = 0 if cw5 == .
replace cw6 = 0 if cw6 == .
replace cw7 = 0 if cw7 == .
replace cw8 = 0 if cw8 == .
replace cw9 = 0 if cw9 == .
replace cw10 = 0 if cw10 == .

gen cwf2 = cw1 if sectoralt  == 1
replace cwf2 = cw1 + cw2 if sectoralt  == 2
replace cwf2 = cw1 + cw2+ cw3 if sectoralt  == 3
replace cwf2 = cw1 + cw2+ cw3 + cw4 if sectoralt  == 4
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 if sectoralt  == 5
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 if sectoralt  == 6
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 + cw7 if sectoralt  == 7
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 + cw7 + cw8 if sectoralt  == 8
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 + cw7 + cw8 + cw9 if sectoralt  == 9
replace cwf2 = cw1 + cw2+ cw3 + cw4 + cw5 + cw6 + cw7 + cw8 + cw9 + cw10 if sectoralt  == 10

sort fundname year sectoralt
gen y = 0
bysort fundname year: replace y = 1 if sectoralt~=sectoralt[_n-1]


log using AF1.log, append
*** Appendix Figure 1 Bottom Panel ***
*** Cumulative Distribution of the Number of Holdings: Global vs. Specialized Funds ***
*** Based on the SIC Classification of Sectors ***
tabstat cumsic3 if fundtype2 == "Global" & y == 1, by(sectoralt) stats(p50)
tabstat cumsic3 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sectoralt) stats(p50)
log close

log using AF2.log, append
*** Appendix Figure 2 Bottom Panel ***
*** Cumulative Distribution of Mutual Fund Assets: Global vs. Specialized Funds ***
*** Based on the SIC Classification of Sectors ***
tabstat cwf2 if fundtype2 == "Global" & y == 1, by(sectoralt) stats(p50)
tabstat cwf2 if (fundtype7 == "Emerging Market"|fundtype7 == "Asia"|fundtype7 == "Europe"|fundtype7 == "Latin America"|fundtype7 == "Country Fund") & y == 1 , by(sectoralt) stats(p50)
log close

drop x total2 pw2 xbar2 ctryhldgsall2 ctryhldgs2 tholdings2 
drop s1a-s10a s1s-s10s S1weight-S10weight
drop sectoralt cumsic3 sw2 cwf2 y
drop cw1a-cw10a cw1-cw10

